package renthouse.dao;/*
 * @创建者：黄苟苟
 * @创建时间：2021/5/18   18:10
 * @Description描述
 **/





import renthouse.entry.Order;

import renthouse.util.ConnDb;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class OrderDao {
    
    public boolean insert(Order order) {
        String s="insert into `order`(houseid,masterid,userid,address,state,style,rent,start,`end`) values (?,?,?,?,?,?,?,?,?)";
        Connection connection= ConnDb.getConnection();
        PreparedStatement p=null;
        try {
            p=connection.prepareStatement(s);
            p.setInt(1,order.getHouseid());
            p.setInt(2,order.getMasterid());
            p.setInt(3,order.getUserid());
            p.setString(4,order.getAddress());
            p.setString(5,order.getState());
            p.setString(6,order.getStyle());
            p.setInt(7,order.getRent());
            p.setString(8,order.getStart());
            p.setString(9,order.getEnd());


            int i=  p.executeUpdate();
            if (i>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,p,null);  //关闭
        }


        return false;
    }

   
    public boolean deleteById(int i) {
        String deletestr="delete from `order` where id="+i;
        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        try {
            int len=statement.executeUpdate(deletestr);
            if (len>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,statement,null);
        }
        return false;
    }
    
    

   
    public boolean updateById(Order order) {
        if (order.getNumber()==0){
            return false;
        }
        String updateStr="update `order` set number="+order.getNumber();
        if (order.getHouseid()!=0){
            updateStr+=",houseid="+order.getHouseid();
        }

        if (order.getMasterid()!=0){
            updateStr+=",masterid="+order.getMasterid();
        }

        if (order.getUserid()!=0){
            updateStr+=",userid="+order.getUserid();
        }

        if (order.getAddress()!=null){
            updateStr+=",address='"+order.getAddress()+"'";
        }

        if (order.getState()!=null){
            updateStr+=",state='"+order.getState()+"'";
        }

        if (order.getStyle()!=null){
            updateStr+=",style='"+order.getStyle()+"'";
        }
        if (order.getRent()!=0){
            updateStr+=",rent="+order.getRent();
        }


        //加上条件

        updateStr+=" where number="+order.getNumber();


        Connection connection= ConnDb.getConnection();
        Statement statement= ConnDb.getStatement(connection);
        try {
            int len=statement.executeUpdate(updateStr);
            if (len>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,statement,null);
        }
        return false;
    }

    
    public List <Order> query(Order order) {
        String str="SELECT * FROM `order` where 1=1 ";
        List<Order> list=new ArrayList <>();
        if (order.getHouseid()!=0){
            str+=" and houseid="+order.getHouseid();
        }

        if (order.getMasterid()!=0){
            str+=" and masterid="+order.getMasterid();
        }

        if (order.getUserid()!=0){
            str+=" and userid="+order.getUserid();
        }

        if (order.getAddress()!=null){
            str+=" and address like concat('%','"+order.getAddress()+"','%')";
        }

        if (order.getState()!=null){
            str+=" and state like concat('%','"+order.getState()+"','%')";
        }

        if (order.getStyle()!=null){
            str+=" and style like concat('%','"+order.getStyle()+"','%')";
        }
        if (order.getRent()!=0){
            str+="and rent="+order.getRent();
        }
        if (order.getStart()!=null){
            str+=" and start like concat('%','"+order.getStart()+"','%')";
        }
        if (order.getEnd()!=null){
            str+=" and end like concat('%','"+order.getEnd()+"','%')";
        }


        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        ResultSet resultSet=null;

        try {
            resultSet=statement.executeQuery(str);
            while (resultSet.next()){
                Order order1=new Order();
                order1.setNumber(resultSet.getInt("number"));
                order1.setHouseid(resultSet.getInt("houseid"));
                order1.setMasterid(resultSet.getInt("masterid"));
                order1.setUserid(resultSet.getInt("userid"));
                order1.setAddress(resultSet.getString("address"));
                order1.setState(resultSet.getString("state"));
                order1.setStyle(resultSet.getString("style"));
                order1.setRent(resultSet.getInt("rent"));
                order1.setStart(resultSet.getString("start"));
                order1.setEnd(resultSet.getString("end"));

                list.add(order1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    
    public List <Order> queryForPage(Order order, int pageNum, int pageSize) {

        int start = (pageNum - 1) * pageSize;
        String str="SELECT * FROM `order` where 1=1 ";
        List<Order> list=new ArrayList <>();
        if (order.getNumber()!=0){
            str+=" and number="+order.getNumber();
        }
        if (order.getHouseid()!=0){
            str+=" and houseid="+order.getHouseid();
        }

        if (order.getMasterid()!=0){
            str+="  and masterid="+order.getMasterid();
        }

        if (order.getUserid()!=0){
            str+=" and userid="+order.getUserid();
        }

        if (order.getAddress()!=null){
            str+=" and address like concat('%','"+order.getAddress()+"','%')";
        }

        if (order.getState()!=null){
            str+=" and state like concat('%','"+order.getState()+"','%')";
        }

        if (order.getStyle()!=null){
            str+=" and style like concat('%','"+order.getStyle()+"','%')";
        }
        if (order.getRent()!=0){
            str+="and rent="+order.getRent();
        }
        if (order.getStart()!=null){
            str+=" and start like concat('%','"+order.getStart()+"','%')";
        }
        if (order.getEnd()!=null){
            str+=" and end like concat('%','"+order.getEnd()+"','%')";
        }

        str += " limit " + start + "," + pageSize;
        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        ResultSet resultSet=null;

        try {
            resultSet=statement.executeQuery(str);
            while (resultSet.next()){
                Order order1=new Order();
                order1.setNumber(resultSet.getInt("number"));
                order1.setHouseid(resultSet.getInt("houseid"));
                order1.setMasterid(resultSet.getInt("masterid"));
                order1.setUserid(resultSet.getInt("userid"));
                order1.setAddress(resultSet.getString("address"));
                order1.setState(resultSet.getString("state"));
                order1.setStyle(resultSet.getString("style"));
                order1.setRent(resultSet.getInt("rent"));
                order1.setStart(resultSet.getString("start"));
                order1.setEnd(resultSet.getString("end"));

                list.add(order1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public int queryCount(Order order) {


        String str = "SELECT count(1) countNum FROM `order` where 1=1 ";
        if (order.getHouseid()!=0){
            str+="and houseid="+order.getHouseid();
        }

        if (order.getMasterid()!=0){
            str+="  and masterid="+order.getMasterid();
        }

        if (order.getUserid()!=0){
            str+=" and userid="+order.getUserid();
        }

        if (order.getAddress()!=null){
            str+=" and address like concat('%','"+order.getAddress()+"','%')";
        }

        if (order.getState()!=null){
            str+=" and state like concat('%','"+order.getState()+"','%')";
        }

        if (order.getStyle()!=null){
            str+=" and style like concat('%','"+order.getStyle()+"','%')";
        }
        if (order.getRent()!=0){
            str+="and rent="+order.getRent();
        }
        if (order.getStart()!=null){
            str+=" and start like concat('%','"+order.getStart()+"','%')";
        }
        if (order.getEnd()!=null){
            str+=" and end like concat('%','"+order.getEnd()+"','%')";
        }


        Connection connection = ConnDb.getConnection();
        Statement statement = ConnDb.getStatement(connection);
        ResultSet resultSet = null;

        int count=0;
        try {
            resultSet = statement.executeQuery(str);
            while (resultSet.next()) {
                count=resultSet.getInt("countNum");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}
